-- @owner: @li-xin12345
-- @date: 2022/5/16
-- @testpoint: 序列

--step1：创建表；expect：成功
drop table if exists t_ustore_orderby_2_049 cascade;
create table t_ustore_orderby_2_049(
	i_int int,
	v_varchar varchar(20),
	bool boolean,
	d_decimal decimal,
	v_char  char(10),
	b_binary bytea,
	d_datetime date,
	t_interval interval day(7) to second,
	array_list int[]
) with (storage_type=ustore);

--step2：插入数据；expect：成功
insert into t_ustore_orderby_2_049 values(1,'aa',true,12.3,'王五','101010','2020-01-01 17:18:18','-1234 0:0:0.0004','{1,2,3}');
insert into t_ustore_orderby_2_049 values(2,'bb',false,12.4,'张天','010101','2021-01-01 17:18:18','-1234 0:0:0.0004','{1,2,3}');
insert into t_ustore_orderby_2_049 values(3,'cc',0,12.5,'张三','010101','2020-01-01 17:18:18','-1234 0:0:0.0004','{1,2}');
insert into t_ustore_orderby_2_049 values(4,'dd',1,12.4,'李四','010101','2021-01-01 17:18:18','-1234 0:0:0.0004','{1,2,3}');
insert into t_ustore_orderby_2_049 values(5,'aa',true,1.3,'王五','101010','2020-01-01 17:18:18','-1234 0:0:0.0004','{1,2,3}');
insert into t_ustore_orderby_2_049 values(6,'dd',false,12.40,'六六','101010','2021-01-01 17:18:18','-1234 0:0:0.0004','{1,2}');

--step3：创建序列;expect：成功
drop sequence if exists seq_orderby_2 cascade;
create sequence seq_orderby_2 start with 10 maxvalue 200 increment by 2 cycle;

--step4：查看表,序列;expect：成功
select * from (select distinct sum(i_int) a,v_char,v_varchar,d_decimal from t_ustore_orderby_2_049 where i_int>1 group by v_char,v_varchar,d_decimal having d_decimal>2) where a>1  order by abs(d_decimal),seq_orderby_2.nextval;
explain plan for select * from (select distinct sum(i_int) a,v_char,v_varchar,d_decimal from t_ustore_orderby_2_049 where i_int>1 group by v_char,v_varchar,d_decimal having d_decimal>2) where a>1  order by abs(d_decimal),seq_orderby_2.nextval;

--step5:清理环境；expect:成功
delete from plan_table;
drop sequence  seq_orderby_2 cascade;
drop table t_ustore_orderby_2_049 cascade;
